﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OracleClient;
using CardLogistics.Model.PutOutCard;

namespace CardLogistics.DAL.PutOutCard
{
    /// <summary>
    /// 半成品/成品卡调拨出库_数据访问类
    /// </summary>
    public class PutOutAllocationData
    {
        #region 查询
        /// <summary>
        /// 根据条件查询相应的表的字段
        /// </summary>
        /// <param name="StrWhere">查询条件</param>
        /// <param name="StrFields">要查询的字段</param>
        /// <returns>填充完数据的DataTable</returns>
        public DataTable GetList(string StrWhere, string StrFields)
        {
            return CommonData.GetListOrderBy(StrWhere, "v_PutOutAllocation", StrFields, "ID", 0);
        }

        /// <summary>
        /// 根据编号查询基本信息
        /// </summary>
        /// <param name="id">编号</param>
        /// <returns>填充完数据的DataTable</returns>
        public DataTable GetInfoById(string id)
        {
            return GetList(" ID = '" + id + "' ", "*");
        }
        /// <summary>
        /// 根据条件查询相应的表的字段
        /// </summary>
        /// <param name="StrWhere">查询条件</param>
        /// <param name="StrFields">要查询的字段</param>
        /// <returns>填充完数据的DataTable</returns>
        public DataTable GetListDetail(string StrWhere, string StrFields)
        {
            return CommonData.GetListOrderBy(StrWhere, "putoutallocation_detail", StrFields, "ID", 0);
        }

        /// <summary>
        /// 根据调拨出库外键查询调拨出库明细
        /// </summary>
        /// <param name="id">编号</param>
        /// <returns>填充完数据的DataTable</returns>
        public DataTable GetInfoByOutAllocationId(string CheckId)
        {
            return GetListDetail(" putoutallocationid= '" + CheckId + "' ", "*");
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public PutOutAllocation GetModel(string rId)
        {
            PutOutAllocation model = new PutOutAllocation();
            DataRow row = GetInfoById(rId).Rows[0];
            if (row != null)
            {
                if (row["ID"] != null)
                {
                    model.ID = row["ID"].ToString();
                }
                if (row["PUTOUTALLOCATIONNO"] != null)
                {
                    model.PUTOUTALLOCATIONNO = row["PUTOUTALLOCATIONNO"].ToString();
                }
                if (row["PUTINALLOCATIONID"] != null)
                {
                    model.PUTINALLOCATIONID = row["PUTINALLOCATIONID"].ToString();
                }
                if (row["ALLOCATIONDATE"] != null && row["ALLOCATIONDATE"].ToString() != "")
                {
                    model.ALLOCATIONDATE = DateTime.Parse(row["ALLOCATIONDATE"].ToString());
                }
                if (row["ALLOCATIONNUMBER"] != null && row["ALLOCATIONNUMBER"].ToString() != "")
                {
                    model.ALLOCATIONNUMBER = decimal.Parse(row["ALLOCATIONNUMBER"].ToString());
                }
                if (row["LEADINGMECHANISM"] != null)
                {
                    model.LEADINGMECHANISM = row["LEADINGMECHANISM"].ToString();
                }
                if (row["REMARKS"] != null)
                {
                    model.REMARKS = row["REMARKS"].ToString();
                }
                if (row["LASTUPDATEUSER"] != null)
                {
                    model.LASTUPDATEUSER = row["LASTUPDATEUSER"].ToString();
                }
                if (row["LASTUPDATEDATE"] != null && row["LASTUPDATEDATE"].ToString() != "")
                {
                    model.LASTUPDATEDATE = DateTime.Parse(row["LASTUPDATEDATE"].ToString());
                }
                if (row["ISDELETE"] != null && row["ISDELETE"].ToString() != "")
                {
                    model.ISDELETE = decimal.Parse(row["ISDELETE"].ToString());
                }
                if (row["ADDTIME"] != null && row["ADDTIME"].ToString() != "")
                {
                    model.ADDTIME = DateTime.Parse(row["ADDTIME"].ToString());
                }
                if (row["IsSubmit"] != null && row["IsSubmit"].ToString() != "")
                {
                    model.IsSubmit = int.Parse(row["IsSubmit"].ToString());
                }

                if (row["PutInAllocationNo"] != null && row["PutInAllocationNo"].ToString() != "")
                {
                    model.PutInAllocationNo = row["PutInAllocationNo"].ToString();//调拨入库单号
                }
                if (row["Salesname"] != null && row["Salesname"].ToString() != "")
                {
                    model.Salesname = row["Salesname"].ToString();//领用机构
                }
                
            }
            return model;
        }
        #endregion

        #region 新增
        /// <summary>
        /// 新增信息
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns>返回插入数据的编号</returns>
        public string Add(PutOutAllocation model)
        {
            try
            {
                OracleParameter[] parameters = {
                    new OracleParameter("varOut", OracleType.VarChar,50),
				    new OracleParameter("vPUTOUTALLOCATIONNO", OracleType.NVarChar),
					new OracleParameter("vPUTINALLOCATIONID", OracleType.NVarChar),
					new OracleParameter("vALLOCATIONDATE", OracleType.DateTime),
					new OracleParameter("vALLOCATIONNUMBER", OracleType.Number,4),
					new OracleParameter("vLEADINGMECHANISM", OracleType.NVarChar),
					new OracleParameter("vREMARKS", OracleType.NVarChar),
					new OracleParameter("vLASTUPDATEUSER", OracleType.VarChar,50),
					new OracleParameter("vLASTUPDATEDATE", OracleType.DateTime),
					new OracleParameter("vISDELETE", OracleType.Number,4),
					new OracleParameter("vADDTIME", OracleType.DateTime),
                    new OracleParameter("vIsSubmit", OracleType.Number,4)};
                parameters[0].Direction = ParameterDirection.Output;
                parameters[1].Value = model.PUTOUTALLOCATIONNO;
                parameters[2].Value = model.PUTINALLOCATIONID;
                parameters[3].Value = model.ALLOCATIONDATE;
                parameters[4].Value = model.ALLOCATIONNUMBER;
                parameters[5].Value = model.LEADINGMECHANISM;
                parameters[6].Value = model.REMARKS;
                parameters[7].Value = model.LASTUPDATEUSER;
                parameters[8].Value = model.LASTUPDATEDATE;
                parameters[9].Value = model.ISDELETE;
                parameters[10].Value = model.ADDTIME;
                parameters[11].Value = model.IsSubmit;
                string StrOutPar = DbHelperSQL.RunProcedure("P_PutOutAllocation_Add", parameters, parameters[0]).ToString();
                return StrOutPar;
            }
            catch (Exception ex)
            {
                return "err," + ex.Message;
            }
        }
        #endregion

        #region 重载新增信息
        /// <summary>
        /// 重载新增信息
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns>返回插入数据的编号</returns>
        public string Add(PutOutAllocation model, string FileValue)
        {
            string StrOutPar = string.Empty;
            int val = 0;
            string sqlConn = DbHelperSQL.ConnectionString;
            using (OracleConnection conn = new OracleConnection(sqlConn))
            {
                conn.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                OracleTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;

                string[] split = FileValue.Split(';');//以分号分隔
                int count = split.Length; //计算行数

                StringBuilder strSql = new StringBuilder();
   
                try
                {
                    string gid = System.Guid.NewGuid().ToString();//主键Id
                    OracleParameter[] parameters = {
                    new OracleParameter("varOut", OracleType.VarChar,50),
				    new OracleParameter("vPUTOUTALLOCATIONNO", OracleType.NVarChar),
					new OracleParameter("vPUTINALLOCATIONID", OracleType.NVarChar),
					new OracleParameter("vALLOCATIONDATE", OracleType.DateTime),
					new OracleParameter("vALLOCATIONNUMBER", OracleType.Number,4),
					new OracleParameter("vLEADINGMECHANISM", OracleType.NVarChar),
					new OracleParameter("vREMARKS", OracleType.NVarChar),
					new OracleParameter("vLASTUPDATEUSER", OracleType.VarChar,50),
					new OracleParameter("vLASTUPDATEDATE", OracleType.DateTime),
					new OracleParameter("vISDELETE", OracleType.Number,4),
					new OracleParameter("vADDTIME", OracleType.DateTime),
                    new OracleParameter("vIsSubmit", OracleType.Number,4),
                    new OracleParameter("vID",OracleType.VarChar)};
                    parameters[0].Direction = ParameterDirection.Output;
                    parameters[1].Value = model.PUTOUTALLOCATIONNO;
                    parameters[2].Value = model.PUTINALLOCATIONID;
                    parameters[3].Value = model.ALLOCATIONDATE;
                    parameters[4].Value = model.ALLOCATIONNUMBER;
                    parameters[5].Value = model.LEADINGMECHANISM;
                    parameters[6].Value = model.REMARKS;
                    parameters[7].Value = model.LASTUPDATEUSER;
                    parameters[8].Value = model.LASTUPDATEDATE;
                    parameters[9].Value = model.ISDELETE;
                    parameters[10].Value = model.ADDTIME;
                    parameters[11].Value = model.IsSubmit;
                    parameters[12].Value = gid;

                        strSql.Append(" begin  ");
                        strSql.Append(" delete from putoutallocation_detail where putoutallocationid ='" + gid + "';");
                        for (int k = 0; k < count; k++)
                        {
                            strSql.Append("insert into putoutallocation_detail");
                            strSql.Append("(putoutallocationid,cardNo,startno,endno,deservednumber,actualnumber,missingcardno)");
                            strSql.Append(" values(");
                            strSql.Append("'" + gid + "',");
                            strSql.Append("" + split[k].Replace('|', ',') + "");
                            strSql.Append(");");

                            #region 插入卡明细
                            strSql.Append("P_CARDRECORD_Add(" + split[k].Split('|')[1] + "," + split[k].Split('|')[2] + "," + split[k].Split('|')[5] + ",0,'" + model.ID + "',");
                            strSql.Append("to_date('" + model.ALLOCATIONDATE.ToString() + "','yyyy-MM-dd HH24:mi:ss'),10," + k + ",0);");
                            #endregion
                        }
                        strSql.Append(" end; ");
                        if (!String.IsNullOrEmpty(strSql.ToString()))
                        {
                            cmd.CommandText = strSql.ToString();
                            val += cmd.ExecuteNonQuery();
                        }
                     StrOutPar = DbHelperSQL.RunProcedure("P_PutOutAllocation_Add", parameters, parameters[0]).ToString();
                     if (StrOutPar.Split(',')[0] == "ok")
                         tx.Commit();
                     else
                         tx.Rollback();
                }
                catch (System.Data.OracleClient.OracleException e)
                {

                    tx.Rollback();
                    throw new Exception(e.Message);
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                }
            }
            return StrOutPar;

        }
        #endregion

        #region 重载修改信息
        /// <summary>
        /// 重载修改信息
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns>返回插入数据的编号</returns>
        public string Update(PutOutAllocation model, string FileValue)
        {
            string StrOutPar = string.Empty;
            int val = 0;
            string sqlConn = DbHelperSQL.ConnectionString;
            using (OracleConnection conn = new OracleConnection(sqlConn))
            {
                conn.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                OracleTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;

                string[] split = FileValue.Split(';');//以分号分隔
                int count = split.Length; //计算行数
                StringBuilder strSql = new StringBuilder();

                try
                {
                    OracleParameter[] parameters = {
                    new OracleParameter("vID", OracleType.VarChar,50),
		            new OracleParameter("vPUTOUTALLOCATIONNO", OracleType.NVarChar),
					new OracleParameter("vPUTINALLOCATIONID", OracleType.NVarChar),
					new OracleParameter("vALLOCATIONDATE", OracleType.DateTime),
					new OracleParameter("vALLOCATIONNUMBER", OracleType.Number,4),
					new OracleParameter("vLEADINGMECHANISM", OracleType.NVarChar),
					new OracleParameter("vREMARKS", OracleType.NVarChar),
					new OracleParameter("vLASTUPDATEUSER", OracleType.VarChar,50),
					new OracleParameter("vLASTUPDATEDATE", OracleType.DateTime),
					new OracleParameter("vISDELETE", OracleType.Number,4),
					new OracleParameter("vADDTIME", OracleType.DateTime),
                    new OracleParameter("vIsSubmit", OracleType.Number,4),
                    new OracleParameter("varOut", OracleType.VarChar,50)};
                    parameters[0].Value = model.ID;
                    parameters[1].Value = model.PUTOUTALLOCATIONNO;
                    parameters[2].Value = model.PUTINALLOCATIONID;
                    parameters[3].Value = model.ALLOCATIONDATE;
                    parameters[4].Value = model.ALLOCATIONNUMBER;
                    parameters[5].Value = model.LEADINGMECHANISM;
                    parameters[6].Value = model.REMARKS;
                    parameters[7].Value = model.LASTUPDATEUSER;
                    parameters[8].Value = model.LASTUPDATEDATE;
                    parameters[9].Value = model.ISDELETE;
                    parameters[10].Value = model.ADDTIME;
                    parameters[11].Value = model.IsSubmit;
                    parameters[12].Direction = ParameterDirection.Output;
               
                        strSql = new StringBuilder();
                        strSql.Append(" begin  ");
                        strSql.Append(" delete from putoutallocation_detail where putoutallocationid ='" + model.ID + "';");
                        for (int k = 0; k < count; k++)
                        {
                            strSql.Append("insert into putoutallocation_detail");
                            strSql.Append("(putoutallocationid,cardNo,startno,endno,deservednumber,actualnumber,missingcardno)");
                            strSql.Append(" values(");
                            strSql.Append("'" + model.ID + "',");
                            strSql.Append("" + split[k].Replace('|', ',') + "");
                            strSql.Append(");");

                            #region 插入卡明细
                            strSql.Append("P_CARDRECORD_Add(" + split[k].Split('|')[1] + "," + split[k].Split('|')[2] + "," + split[k].Split('|')[5] + ",0,'" + model.ID + "',");
                            strSql.Append("to_date('" + model.ALLOCATIONDATE.ToString() + "','yyyy-MM-dd HH24:mi:ss'),10," + k + ",0);");
                            #endregion
                        }
                        strSql.Append(" end; ");
                        if (!String.IsNullOrEmpty(strSql.ToString()))
                        {
                            cmd.CommandText = strSql.ToString();
                            val += cmd.ExecuteNonQuery();
                           
                        }
                     StrOutPar = DbHelperSQL.RunProcedure("P_PutOutAllocation_Update", parameters, parameters[12]).ToString();
                    if (StrOutPar.Split(',')[0] == "ok")
                         tx.Commit();
                   else
                        tx.Rollback();

                }
                catch (System.Data.OracleClient.OracleException e)
                {
                    tx.Rollback();
                    throw new Exception(e.Message);
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                }
            }
            return StrOutPar;
        }
        #endregion

        #region 修改
        /// <summary>
        /// 修改信息
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns>返回插入数据的编号</returns>
        public string Update(PutOutAllocation model)
        {
            try
            {
                OracleParameter[] parameters = {
                    new OracleParameter("vID", OracleType.VarChar,50),
		            new OracleParameter("vPUTOUTALLOCATIONNO", OracleType.NVarChar),
					new OracleParameter("vPUTINALLOCATIONID", OracleType.NVarChar),
					new OracleParameter("vALLOCATIONDATE", OracleType.DateTime),
					new OracleParameter("vALLOCATIONNUMBER", OracleType.Number,4),
					new OracleParameter("vLEADINGMECHANISM", OracleType.NVarChar),
					new OracleParameter("vREMARKS", OracleType.NVarChar),
					new OracleParameter("vLASTUPDATEUSER", OracleType.VarChar,50),
					new OracleParameter("vLASTUPDATEDATE", OracleType.DateTime),
					new OracleParameter("vISDELETE", OracleType.Number,4),
					new OracleParameter("vADDTIME", OracleType.DateTime),
                    new OracleParameter("vIsSubmit", OracleType.Number,4),
                    new OracleParameter("varOut", OracleType.VarChar,50)};
                parameters[0].Value = model.ID;
                parameters[1].Value = model.PUTOUTALLOCATIONNO;
                parameters[2].Value = model.PUTINALLOCATIONID;
                parameters[3].Value = model.ALLOCATIONDATE;
                parameters[4].Value = model.ALLOCATIONNUMBER;
                parameters[5].Value = model.LEADINGMECHANISM;
                parameters[6].Value = model.REMARKS;
                parameters[7].Value = model.LASTUPDATEUSER;
                parameters[8].Value = model.LASTUPDATEDATE;
                parameters[9].Value = model.ISDELETE;
                parameters[10].Value = model.ADDTIME;
                parameters[11].Value = model.IsSubmit;
                parameters[12].Direction = ParameterDirection.Output;
                string StrOutPar = DbHelperSQL.RunProcedure("P_PutOutAllocation_Update", parameters, parameters[12]).ToString();
                return StrOutPar;
            }
            catch (Exception ex)
            {
                return "err," + ex.Message;
            }
        }
        #endregion

        #region 删除
        /// <summary>
        /// 删除信息
        /// </summary>
        /// <param name="id">删除信息的ID</param>
        /// <returns>删除成功返回True。否则返回false</returns>
        public string Delete(string id)
        {
            try
            {
                OracleParameter[] parameters = {
                        new OracleParameter("varOut",  OracleType.VarChar,100),
					    new OracleParameter("vid",  OracleType.VarChar,50)
                };
                parameters[0].Direction = ParameterDirection.Output;
                parameters[1].Value = id;
                string StrOutPar = DbHelperSQL.RunProcedure("P_PutOutAllocation_Delete", parameters, parameters[0]).ToString();
                return StrOutPar;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion
    }
}
